package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.common.*;
import com.iris.live.services.common.KPIMetaData.TYPE;
import com.iris.live.services.data.repositories.ImgtRepository;
import com.iris.live.services.data.repositories.Lsh库存表Repository;
import com.iris.live.services.data.repositories.Lsh经销商表Repository;
import com.iris.live.services.data.repositories.Lsh车型Repository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.ResultData;
import com.iris.live.services.services.ImgtService;
import com.iris.live.services.services.SetService;
import com.iris.live.services.services.kpi.KPIInitializer;
import org.apache.logging.log4j.ThreadContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Repository;

import javax.persistence.Query;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.*;

/**
 *
 */
@Repository
public class ImgtRepositoryImpl extends BaseRepository implements ImgtRepository {
    private static final Logger logger = LoggerFactory.getLogger(ImgtRepositoryImpl.class);

    private static DecimalFormat df_1 = new DecimalFormat("#0.0");
    private static DecimalFormat df_4 = new DecimalFormat("#0.0000");
    private static DecimalFormat df_int = new DecimalFormat("#0");

    private static BigDecimal bd100 = new BigDecimal(100);

    @Autowired
    private Environment env;

    @Autowired
    private KPIInitializer kpiInitializer;

    @Autowired
    private Lsh经销商表Repository lsh经销商表Repository;

    @Autowired
    private Lsh库存表Repository lsh库存表Repository;

    @Autowired
    private Lsh车型Repository lsh车型Repository;

    @Autowired
    private SetService setService;

    private Map<String, String> orgMapping = new HashMap<>();

    public ImgtRepositoryImpl() {
        orgMapping.put("集团", "group_code");
        orgMapping.put("大区", "b_region_code");
        orgMapping.put("小区", "s_region_code");
        orgMapping.put("CTA", "pma_code");
        orgMapping.put("许可_BMBS", "permit");
    }

    @Override
    public Map KPI(String name, String startDate, String endDate, String role,
                   String roleCode, String areaRole, String areaCode,
                   String brand, String actualValue,
                   String actualValueCode) {
        return this.getKPI1(name, startDate, endDate, role, roleCode, areaRole,
                areaCode, brand, actualValue,
                actualValueCode);
    }

    @Override
    public List getStoreKpiDataWhenDealer(String name, List<String> list, String brand, String permit) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT  " +
                "a.车型详细 as modeldetail, " +
                "DATEDIFF('2020-01-01', 批发日期) carstockage, " +
                "b.颜色代码 carappcode, " +
                "b.内饰代码 carinncode, " +
                "b.实际MSRP含税 caramsrp, " +
                "b.车辆零售状态 carstate, " +
                "b.车架号 carvin," +
                "IF(advance_sales_days is NULL,'否','是') isadvancesales  ");

        sql.append("	      FROM   LSH车型 a, LSH库存表 b     " +
                "	      WHERE b.批发日期 <= '2020-01-01' and b.is_delete = 0 and b.车架号 not like 'IRIS%' and a.车型编号 = b.车型编号  " +
                "         AND NOT EXISTS ( " +
                "                SELECT c.库存编号 " +
                "                 FROM vw_stock_quotation c " +
                "                WHERE " +
                "                 (" +
                "                   (c.开票时间 <= '2020-01-01 23:59:59' and c.退票时间 IS NULL)" +
                "                   or (c.开票时间 <= '2020-01-01 23:59:59' and c.退票时间 < c.开票时间)" +
                "                 )   " +
                "                   AND c.库存编号 = b.库存编号" +
                "         )      " +
                "	      AND (case when 'MB' = '' then true else a.品牌 = 'MB' end)   " +
                "         and b.所属经销商 in (select 许可_bmbs from lsh经销商表 where salesCode = (select salesCode from lsh经销商表 where 许可_bmbs = '"
        ).append(permit).append("'))");
        if ("180天以上长库龄".equals(name)) {
            sql.append(" and IF(DATEDIFF('2020-01-01', 批发日期) >= 180, 1 , 0) ");
        } else if ("120天以上长库龄".equals(name)) {
            sql.append(" and IF(DATEDIFF('2020-01-01', 批发日期) >= 120 && DATEDIFF('2020-01-01', 批发日期) < 180, 1 , 0) ");
        } else if ("90天以上长库龄".equals(name)) {
            sql.append(" and IF(DATEDIFF('2020-01-01', 批发日期) >= 90 && DATEDIFF('2020-01-01', 批发日期) < 120, 1 , 0) ");
        } else if ("60天以上长库龄".equals(name)) {
            sql.append(" and IF(DATEDIFF('2020-01-01', 批发日期) >= 60 && DATEDIFF('2020-01-01', 批发日期) < 90, 1 , 0) ");
        } else {
            logger.error("getStoreKpiDataWhenDealer，不支持的参数类型");
            return null;
        }
        sql.append(" order BY advance_sales_days DESC ,DATEDIFF('2017-08-21', 批发日期) DESC");
        return getList(sql.toString().replace("'MB'", "".equals(brand) ? "''" : "'" + brand + "'")
                .replace("2000-01-01", list.get(0))
                .replace("2020-01-01", list.get(1)));
    }

    @Override
    public List getSalesAndGpKpiWhenDealer(String name, String beginDate, String endDate, String role, String roleCode, String areaRole, String areaCode, String brand) {
        KPIMetaData kpiMetaData = new KPIUtils().getCnToEn(name);

        String dealerName = isChineseLanguage()? "permit_cn" : "permit_EN";

        StringBuilder sql = new StringBuilder("select ").append(" permit, ").append(dealerName).append(" as permit_name, sum(").append(kpiMetaData.getNumerator())
                .append(") as numerator, ");
        if (kpiMetaData.getType() == 1) {
            sql.append(", 1 denominator ");
        } else {
            sql.append("sum(").append(kpiMetaData.getDenominator()).append(") denominator ");
        }

        String tableName;
        if(beginDate.equals(endDate)){
            tableName = String.format("T_BI_DAILY_STAT_DATA_%s", beginDate.substring(0,4));
        }else{
            tableName = "T_BI_MONTHLY_STAT_DATA";
        }

        sql.append(" from ").append(tableName).append(" a WHERE (a.vehicle_level is not null and a.vehicle_level <> 'null' and a.vehicle_level <>'') ");
        // 根据对标值过滤数据
        if ("cta".equalsIgnoreCase(areaRole)) {
            sql.append(" AND (pma_code = '").append(areaCode).append("' OR PMA='").append(areaCode).append("')");
        } else if ("大区".equals(areaRole)) {
            sql.append(" AND (b_region_code = '").append(areaCode).append("' OR b_region='").append(areaCode).append("')");
        }

        //根据时间过滤
        if(beginDate.equals(endDate)) { //月度表
            sql.append(" and stat_date='").append(beginDate).append("'");
        }else{ //年度表
            String fromMonth = beginDate.replaceAll("-","").substring(0, 6),
                    endMonth = endDate.replaceAll("-","").substring(0, 6);

            sql.append(" and stat_date>='").append(fromMonth).append("'").append(" and stat_date<='").append(endMonth).append("'");
        }


        // 按许可分组
        sql.append(" group by sales_code ");

        // 运行KPIUtils中的SQL
        List tmpList = this.getList(sql.toString().replace("'MB'", "".equals(brand) ? "''" : "'" + brand + "'")
                .replace("2000-01-01", beginDate)
                .replace("2020-01-01", endDate));
        // 计算List中各rate
        for (Object o : tmpList) {
            Map<String, Object> m = (Map) o;
            double dNumerator, dDenominator;
            dNumerator = m.get("numerator") == null ? 0 : Double.parseDouble(m.get("numerator").toString());
            dDenominator = m.get("denominator") == null ? 0 : Double.parseDouble(m.get("denominator").toString());
            if (kpiMetaData.getType() == 1) {
                m.put("rate", m.get("numerator"));
            } else if (kpiMetaData.getType() == 2) {
                if (dNumerator != 0) {
                    if("销售目标与新增展厅客流比".equals(name)) {
                        m.put("rate", "1:" + NumberUtils.round(dDenominator / dNumerator, 1));
                    }else {
                        m.put("rate", NumberUtils.round(dNumerator * 100 / dDenominator, 1));
                    }
                } else {
                    m.put("rate", 0.0);
                }
            } else {
                if (dNumerator != 0) {
                    m.put("rate", NumberUtils.round(dNumerator / dDenominator, 1));
                } else {
                    m.put("rate", 0.0);
                }
            }
        }

        // 修改返回字段与原接口兼容
        List<Map<String, Object>> reList = new ArrayList<>();
        for (Object o : tmpList) {
            Map m = (Map) o;
            Map<String, Object> reMap = new HashMap<>();
            reMap.put("许可_bmbs", m.get("permit"));
            reMap.put("经销商名", m.get("permit_name"));
            reMap.put("rate", m.get("rate"));
            reList.add(reMap);
        }
        // 排序
        if(! "销售目标与新增展厅客流比".equals(name)) {
            reList.sort((m1, m2) -> ((Double) m2.get("rate")).compareTo((Double) m1.get("rate")));
        }
        // 添加r字段
        int i = 1;
        for (Map<String, Object> m : reList) {
            m.put("r", i++);
            // rate 设置为string格式
            m.replace("rate", String.valueOf(m.get("rate")));
        }
        return reList;
    }

    @Override
    public ResultData getStoreKPISecondPage(List<String> list, ImgtService.KPIPage items) {
        //获取KPI统计指标定义脚本
        KPIUtils kpiUtils = new KPIUtils();
        KPIMetaData meta = kpiUtils.getCnToEn(items.name);
        if (meta == null) {
            logger.warn(items.name + "相关脚本没有配置.");
            return null;
        }

        ResultData rd = new ResultData();
        PageData pg = new PageData();
        try {
            String sql;
            if ("1".equals(items.modelType)) { //车型模式
                sql  = getSecondPageForVehicleModel(meta, list, items, "numerator");
            } else { //分级模式
                sql = getSecondPageForOrgLevel(meta, list, items, "numerator");
            }

            List data = this.getList(sql.replace("'MB'", "".equals(items.brand) ? "''" : "'" + items.brand + "'")
                    .replace("2000-01-01", list.get(0))
                    .replace("2020-01-01", list.get(1)));

            this.processStoreResult(data, meta, items);

            pg.setData(data);
            pg.setTotal(data.size());
        } catch(Exception e) {
            logger.warn("查询出错-" + items.name, e);
            rd.setResult("false");
        }

        rd.setPageData(pg);
        rd.setMessage("查询成功");
        rd.setResult("true");
        return rd;
    }

    /**
     *
     * @param data
     * @param meta
     * @throws Exception
     */
    private void processStoreResult(List data, KPIMetaData meta,ImgtService.KPIPage items) throws Exception{
        String itemName;
        if ("1".equals(items.modelType)) {
            itemName = "vehicle_level";
        }else{
            if("1".equals(items.areaRank)){
                itemName = isChineseLanguage()? "b_region" : "b_region_code";
            }else if("2".equals(items.areaRank)){
                itemName = isChineseLanguage()? "pma" : "pma_code";
            }else{
                itemName = isChineseLanguage()? "permit_cn" : "permit_en";
            }
        }

        Map resMap = null;
        Double numerator = 0d,denominator = 0d;

        for (Object o : data) {
            resMap = (Map) o;
            if( resMap.get("numerator") != null) {
                numerator = Double.valueOf(resMap.get("numerator").toString());
            }else{
                numerator = 0d;
            }

            if( resMap.get("denominator") != null) {
                denominator = Double.valueOf(resMap.get("denominator").toString());
            }else{
                denominator = 0d;
            }

            resMap.put("firstColumnRole", resMap.get(itemName));

            try {
                if(denominator.intValue() == 0){
                    resMap.put("carSum", 0);
                    resMap.put("rate", 0);
                }else{
                    resMap.put("carSum", NumberUtils.roundInt(numerator.doubleValue()));
                    resMap.put("rate", NumberUtils.round(numerator * 100 / denominator, 1) + "%");
                }
            }catch(Exception exc){
                logger.error("numerator=" + numerator + ",denominator=" + denominator, exc);
            }
        }
    }

    public ResultData getSalesAndGpKPISecondPage(List<String> list, ImgtService.KPIPage items) {
        //获取KPI统计指标定义脚本
        KPIUtils kpiUtils = new KPIUtils();
        KPIMetaData meta = kpiUtils.getCnToEn(items.name);
        if (meta == null) {
            logger.warn(items.name + "相关脚本没有配置.");
            return null;
        }

        ResultData rd = new ResultData();
        PageData pg = new PageData();
        try {
            String sql;
            if ("1".equals(items.modelType)) { //车型模式
                sql  = getSecondPageForVehicleModel(meta, list, items, "ordered_column");
            } else { //分级模式
                sql = getSecondPageForOrgLevel(meta, list, items, "ordered_column");
            }

            List data = this.getList(sql.replace("'MB'", "".equals(items.brand) ? "''" : "'" + items.brand + "'")
                    .replace("2000-01-01", list.get(0))
                    .replace("2020-01-01", list.get(1)));

            this.processResult(data, meta, items);

            pg.setData(data);
            pg.setTotal(data.size());
        } catch(Exception e) {
            logger.warn("查询出错-" + items.name, e);
            rd.setResult("false");
        }

        rd.setPageData(pg);
        rd.setMessage("查询成功");
        rd.setResult("true");
        return rd;
    }

    /**
     *
     * @param data
     * @param meta
     * @throws Exception
     */
    private void processResult(List data, KPIMetaData meta,ImgtService.KPIPage items) throws Exception{
        String itemName;
        if ("1".equals(items.modelType)) {
            itemName = "vehicle_level";
        }else{
            if("1".equals(items.areaRank)){
                itemName = isChineseLanguage()? "b_region" : "b_region_code";
            }else if("2".equals(items.areaRank)){
                itemName = isChineseLanguage()? "pma" : "pma_code";
            }else{
                itemName = isChineseLanguage()? "permit_cn" : "permit_en";
            }
        }

        for (Object o : data) {
            Map resMap = (Map) o;
            Double numerator,denominator;

            if( resMap.get("numerator") != null) {
                numerator = Double.valueOf(resMap.get("numerator").toString());
            }else{
                numerator = 0d;
            }

            if( resMap.get("denominator") != null) {
                denominator = Double.valueOf(resMap.get("denominator").toString());
            }else{
                denominator = 0d;
            }
            resMap.put("firstColumnRole", resMap.get(itemName));

            try {
                if (meta.getType() == 1) {
                    if (meta.getResultType().equals(TYPE.INT)) {
                        resMap.put("carSum", 0);
                        resMap.put("rate", 0);
                    } else {
                        resMap.put("carSum", NumberUtils.round(numerator.doubleValue(), 1));
                        resMap.put("rate", NumberUtils.round(numerator.doubleValue(), 1));
                    }
                } else if (meta.getType() == 2) {
                    if(Math.abs(denominator.doubleValue()) == 0 || Math.abs(numerator.doubleValue()) == 0){
                        resMap.put("carSum", 0);
                        if("销售目标与新增展厅客流比".equalsIgnoreCase(items.name)){
                            resMap.put("rate", "0:0");
                        }else {
                            resMap.put("rate", 0);
                        }
                    }else{
                        resMap.put("carSum", NumberUtils.round(numerator.doubleValue(), 1));
                        if("销售目标与新增展厅客流比".equalsIgnoreCase(items.name)){
                            resMap.put("rate", "1:" + NumberUtils.round(denominator/numerator, 1));
                        }else {
                            resMap.put("rate", NumberUtils.round(numerator * 100 / denominator, 1) + "%");
                        }
                    }
                } else if (meta.getType() == 3) {
                    if(denominator.intValue() == 0){
                        resMap.put("carSum", 0);
                        resMap.put("rate", 0);
                    } else {
                        resMap.put("carSum", NumberUtils.round(numerator.doubleValue(), 1));
                        resMap.put("rate", NumberUtils.round(numerator / denominator, 1));
                    }
                }
            }catch(Exception exc){
                logger.error("numerator=" + numerator + ",denominator=" + denominator, exc);
            }
        }
    }

    /**
     * 获取分车系模式统计脚本
     * @param meta
     * @param list
     * @param items
     * @return
     * @throws Exception
     */
    private String getSecondPageForVehicleModel(KPIMetaData meta, List<String> list, ImgtService.KPIPage items, String ordered_column){
        if(logger.isDebugEnabled()){
            logger.debug("meta={}, list={}, items={}, ordered_column={}", JsonObjectUtils.createJsonString(meta),JsonObjectUtils.createJsonString(list),JsonObjectUtils.createJsonString(items), ordered_column);
        }

        StringBuilder sql = new StringBuilder("select vehicle_level, SUM(").append(meta.getNumerator()).append(") as numerator, ");
        if (meta.getType() == 1) {
            sql.append("1 denominator,  SUM(").append(meta.getNumerator()).append(") as ordered_column ");
        } else {
            sql.append("SUM(").append(meta.getDenominator()).append(") as denominator,  SUM(").append(meta.getNumerator()).append(")/SUM(").append(meta.getDenominator()).append(") as ordered_column ");
        }

        String tableName;
        if(items.timeStatus.equals("DTD")){
            tableName = String.format("T_BI_DAILY_STAT_DATA_%s", items.startDate.substring(0, 4));
        }else{
            tableName = "T_BI_MONTHLY_STAT_DATA";
        }
        sql.append(" from ").append(tableName).append(" a where (a.vehicle_level is not null and a.vehicle_level <> 'null' and a.vehicle_level <>'') ");

        if(! StringUtils.isNullOrBlank(items.brand)){
            sql.append(" and vehicle_brand ='").append(items.brand).append("'");
        }

        //根据时间过滤
        if( list.get(0).equals( list.get(1))) { //月度表
            sql.append(" and stat_date='").append( list.get(0)).append("'");
        }else{ //年度表
            String fromMonth =  list.get(0).replaceAll("-", "").substring(0, 6),
                    endMonth =  list.get(1).replaceAll("-", "").substring(0, 6);

            sql.append(" and stat_date>='").append(fromMonth).append("'").append(" and stat_date<='").append(endMonth).append("'");
        }

        if (! ("total".equalsIgnoreCase(items.actualRole) || "集团".equalsIgnoreCase(items.actualRole) || "LSH".equalsIgnoreCase(items.actualRole))) {
            sql.append(" and ").append(this.orgMapping.get(items.actualRole)).append("='").append(items.actualValueCode).append("'");
        }
        sql.append(" GROUP BY vehicle_level ORDER BY ordered_column DESC");

        if(logger.isDebugEnabled()){
            logger.debug("sql={}", sql.toString());
        }
        return sql.toString();
    }

    /**
     * 获取分级模式统计脚本
     * @param meta
     * @param list
     * @param items
     * @return
     * @throws Exception
     */
    private String getSecondPageForOrgLevel(KPIMetaData meta, List<String> list, ImgtService.KPIPage items, String ordered_column){
        if(logger.isDebugEnabled()){
            logger.debug("meta={}, list={}, items={}, ordered_column={}", JsonObjectUtils.createJsonString(meta),JsonObjectUtils.createJsonString(list),JsonObjectUtils.createJsonString(items), ordered_column);
        }
        String itemName = null,groupItem = null;
        String areaRank = items.areaRank;

        if("1".equals(areaRank)){
            itemName = isChineseLanguage()? "b_region" : "b_region_code";
            groupItem = itemName;
        }else if("2".equals(areaRank)){
            itemName = isChineseLanguage()? "pma" : "pma_code";
            groupItem = itemName;
        }else{
            itemName = isChineseLanguage()? "permit_cn" : "permit_en";
            groupItem = "sales_code";
        }

        StringBuilder sql = new StringBuilder("select ").append(itemName).append(", SUM(").append(meta.getNumerator())
                                                        .append(") as numerator, ");
        if (meta.getType() == 1) {
            sql.append("1 denominator,  SUM(").append(meta.getNumerator()).append(") as ordered_column ");
        } else {
            sql.append("SUM(").append(meta.getDenominator()).append(") as denominator,  SUM(").append(meta.getNumerator()).append(")/SUM(").append(meta.getDenominator()).append(") as ordered_column ");
        }

        String tableName;
        if(items.timeStatus.equals("DTD")){
            tableName = String.format("T_BI_DAILY_STAT_DATA_%s", items.startDate.substring(0, 4));
        }else{
            tableName = "T_BI_MONTHLY_STAT_DATA";
        }
        sql.append(" from ").append(tableName).append(" a where (a.vehicle_level is not null and a.vehicle_level <> 'null' and a.vehicle_level <>'')");

        if(! StringUtils.isNullOrBlank(items.brand)){
            sql.append(" and vehicle_brand ='").append(items.brand).append("'");
        }

        //根据时间过滤
        if( list.get(0).equals( list.get(1))) { //月度表
            sql.append(" and stat_date='").append( list.get(0)).append("'");
        }else{ //年度表
            String fromMonth =  list.get(0).replaceAll("-", "").substring(0, 6),
                    endMonth =  list.get(1).replaceAll("-", "").substring(0, 6);

            sql.append(" and stat_date>='").append(fromMonth).append("'").append(" and stat_date<='").append(endMonth).append("'");
        }

        sql.append(" and a.").append(this.orgMapping.get(items.areaRole)).append("='").append(items.areaCode).append("'");

        sql.append(" GROUP BY ").append(groupItem).append(" ORDER BY ordered_column DESC");
        if(logger.isDebugEnabled()){
            logger.debug("sql={}", sql.toString());
        }
        return sql.toString();
    }

    @Override
    public List KPI2(String name, String startDate, String endDate, String role, String roleCode, String areaRole, String areaCode, String brand) {
        return this.getKPI2(name, startDate, endDate, role, roleCode, areaRole, areaCode, brand);
    }

    private List getKPI2(String name, String startDate, String endDate, String role, String roleCode, String areaRole, String areaCode, String brand) {
        KPIUtils kpiUtils = new KPIUtils();
        KPIMetaData meta = kpiUtils.getCnToEn(name);
        if (meta == null) {
            logger.warn(name + "相关脚本没有配置.");
            return null;
        }
        KPIMetaData[] metas = new KPIMetaData[]{meta};

        //数据过滤
        String sql = this.getKPI2Statement(startDate, endDate, areaRole, areaCode, brand, metas);
        List<Map<String, Object>> l = getList(sql);
        l = filter(l, areaRole, areaCode);

        int index = 1;
        List<Map<String, Object>> result = new ArrayList();
        for (Map<String, Object> value : l) {
            Map<String, Object> newMap = new HashMap();

            newMap.put("许可_bmbs", value.get("许可_BMBS"));
            newMap.put("经销商名", value.get("经销商名"));
            newMap.put("r", index++);

            if (meta.getType() == 1) {
                if(TYPE.INT.equals(meta.getResultType())){
                    newMap.put("rate", df_int.format(value.get(meta.getNumerator()) == null ? 0 : value.get(meta.getNumerator())));
                }else{
                    newMap.put("rate", df_1.format(value.get(meta.getNumerator()) == null ? 0 : value.get(meta.getNumerator())));
                }
            }else if (meta.getType() == 2) {
                Object rate = value.get("rate");
                if (rate == null) {
                    newMap.put("rate", 0);
                }else{
                    if(TYPE.INT.equals(meta.getResultType())){
                        newMap.put("rate", df_int.format(rate == null ? 0 : NumberUtils.roundInt(100 * Double.parseDouble(rate.toString()))));
                    }else{
                        newMap.put("rate", df_1.format(rate == null ? 0 : NumberUtils.round(100 * Double.parseDouble(rate.toString()), 1)));
                    }
                }
            } else {
                Object rate = value.get("rate");
                if(TYPE.INT.equals(meta.getResultType())){
                    newMap.put("rate", df_int.format(rate == null ? 0 : rate));
                }else{
                    newMap.put("rate", df_1.format(rate == null ? 0 : rate));
                }
            }
            result.add(newMap);
        }
        return result;
    }

    /**
     *
     * @param name
     * @param startDate
     * @param endDate
     * @param role
     * @param roleCode
     * @param areaRole  对标类型
     * @param areaCode  对标值
     * @param brand
     * @param actualValue  实际值
     * @param actualValueCode  实际类型
     * @return
     */
    private Map getKPI1(String name, String startDate, String endDate, String role, String roleCode, String areaRole, String areaCode, String brand, String actualValue,
                        String actualValueCode) {
        String[] names = name.split("_");
        KPIMetaData[] metas = new KPIMetaData[names.length];
        KPIUtils kpiUtils = new KPIUtils();
        for(int index = 0; index < names.length; index++){
            KPIMetaData  meta = kpiUtils.getCnToEn(names[index]);
            if (meta == null) {
                logger.warn(names[0] + " 没有配置计算公式。");
                return null;
            }

            metas[index] = meta;
        }

        Map<String, String> orgMapping = new HashMap<>();
        orgMapping.put("集团", "group_code");
        orgMapping.put("大区", "b_region_code");
        orgMapping.put("小区", "s_region_code");
        orgMapping.put("CTA", "pma_code");
        orgMapping.put("许可_BMBS", "permit");

        List l = null;
        try {
            String sql = this.getKPI1Statement(startDate, endDate, areaRole, areaCode, actualValue, brand, metas);
            l = getList(sql);
            l = filter(l, areaRole, areaCode);
        }catch(Exception e){
            logger.warn("name=" + name, e);
            l = new ArrayList<>();
        }

        Map<String, Map> results = new HashMap<>();
        KPIMetaData meta = null;
           for(int index = 0; index < metas.length; index++) {
            Map<String, Object> result = null;
            meta = metas[index];
            name = names[index];

             if (meta.getType() == 1) {
                result = getSummaryStat(l, meta, name, role, roleCode, areaRole, areaCode, actualValue,
                        actualValueCode, brand, startDate, endDate);
            } else if (meta.getType() == 2) {
                result = getRatioStat(l, meta, name, role, roleCode, areaRole, areaCode, actualValue,
                        actualValueCode, brand, startDate, endDate);
            } else if (meta.getType() == 3) {
                result = getUnitStat(l, meta, name, role, roleCode, areaRole, areaCode, actualValue,
                        actualValueCode, brand, startDate, endDate);
            } else {
                throw new IllegalArgumentException("meta.getType()=" + meta.getType() + " is not supported.");
            }

            result.put("name", name);
            results.put(name, result);
        }

        if(logger.isDebugEnabled()){
            logger.debug("name={}, values={}", name, JsonObjectUtils.createJsonString(results));
        }
        return results;
    }

    /**
     * 获取查询脚本
     * @param startDate
     * @param endDate
     * @param areaRole
     * @param areaCode
     * @param actualValue
     * @param brand
     * @param metas
     * @return
     */
    private String getKPI1Statement(String startDate, String endDate, String areaRole, String areaCode, String actualValue, String brand, KPIMetaData[] metas){
        Map<String, String> orgMapping = new HashMap<>();
        orgMapping.put("集团", "group_code");
        orgMapping.put("大区", "b_region_code");
        orgMapping.put("小区", "s_region_code");
        orgMapping.put("CTA", "pma_code");
        orgMapping.put("许可_BMBS", "permit");
        orgMapping.put("许可_bmbs", "permit");

        Set<String> columns = new HashSet<>();

        // 在SQL脚本中添加对标字段，实际字段
        StringBuffer sql = new StringBuffer("select ").append(orgMapping.get(areaRole)).append(" as ").append(areaRole);
        if(! actualValue.equalsIgnoreCase(areaRole)){
            sql.append(",").append(orgMapping.get(actualValue)).append(" as ").append(actualValue);
        }

        for(KPIMetaData meta : metas){ //动态添加指标字段
            if(! columns.contains(meta.getNumeratorAlias())){
                sql.append(",sum(").append(meta.getNumerator()).append(") as ").append(meta.getNumeratorAlias());
                columns.add(meta.getNumeratorAlias());
            }

            if(! columns.contains(meta.getDenominatorAlias()) && ! StringUtils.isNullOrBlank(meta.getDenominator())){
                sql.append(",sum(").append(meta.getDenominator()).append(") as ").append(meta.getDenominator());
                columns.add(meta.getDenominatorAlias());
            }
        }

        String tableName;
        if(startDate.equals(endDate)){
            tableName = String.format("T_BI_DAILY_STAT_DATA_%s", startDate.substring(0,4));
        }else{
            tableName = "T_BI_MONTHLY_STAT_DATA";
        }
        sql.append(" from ").append(tableName).append(" WHERE 1=1 ");

        //根据对标值进行过滤
        if(! areaCode.equalsIgnoreCase("LSH")){
            sql.append(" and ").append(orgMapping.get(areaRole)).append("='").append(areaCode).append("'");
        }

        //根据品牌进行过滤
        if(! StringUtils.isNullOrBlank(brand)){
            sql.append(" and vehicle_brand='").append(brand).append("'");
        }

        //根据时间过滤
        if(startDate.equals(endDate)) { //月度表
            sql.append(" and stat_date='").append(startDate).append("'");
        }else{ //年度表
            String fromMonth = startDate.replaceAll("-","").substring(0, 6),
                    endMonth = endDate.replaceAll("-","").substring(0, 6);

            sql.append(" and stat_date>='").append(fromMonth).append("'").append(" and stat_date<='").append(endMonth).append("'");
        }

        sql.append(" GROUP BY ").append(orgMapping.get(areaRole));
        if(! actualValue.equalsIgnoreCase(areaRole)){
            sql.append(",").append(orgMapping.get(actualValue));
        }

        return sql.toString();
    }

    /**
     * 获取KPI2查询脚本
     * @param startDate
     * @param endDate
     * @param areaRole
     * @param areaCode
     * @param brand
     * @param metas
     * @return
     */
    private String getKPI2Statement(String startDate, String endDate, String areaRole, String areaCode, String brand, KPIMetaData[] metas){
        Map<String, String> orgMapping = new HashMap<>();
        orgMapping.put("集团", "group_code");
        orgMapping.put("大区", "b_region_code");
        orgMapping.put("小区", "s_region_code");
        orgMapping.put("CTA", "pma_code");
        orgMapping.put("许可_BMBS", "permit");

        // 在SQL脚本中添加对标字段，实际字段
        StringBuffer sql = new StringBuffer("select permit, permit_en, permit_code");

        for(KPIMetaData meta : metas){ //动态添加指标字段
            sql.append(meta.getNumerator()).append(" as ").append(meta.getNumeratorAlias());

            if(! StringUtils.isNullOrBlank(meta.getDenominator())){
                sql.append(",").append(meta.getDenominator()).append(" as ").append(meta.getDenominator());
            }
        }

        String tableName;
        if(startDate.equals(endDate)){
            tableName = String.format("T_BI_DAILY_STAT_DATA_%s", startDate.substring(0,4));
        }else{
            tableName = "T_BI_MONTHLY_STAT_DATA";
        }
        sql.append(" from ").append(tableName).append(" WHERE(a.vehicle_level is not null and a.vehicle_level <> 'null' and a.vehicle_level <>'') ");

        //根据对标值进行过滤
        if(! areaCode.equalsIgnoreCase("LSH")){
            sql.append(" and ").append(orgMapping.get(areaRole)).append("='").append(areaCode).append("'");
        }

        //根据品牌进行过滤
        if(! StringUtils.isNullOrBlank(brand)){
            sql.append(" and vehicle_brand='").append(brand).append("'");
        }

        //根据时间过滤
        if(startDate.equals(endDate)) { //月度表
            sql.append(" and stat_date='").append(startDate).append("'");
        }else{ //年度表
            String fromMonth = startDate.replaceAll("-","").substring(0, 6),
                    endMonth = endDate.replaceAll("-","").substring(0, 6);

            sql.append(" and stat_date>='").append(fromMonth).append("'").append(" and stat_date<='").append(endMonth).append("'");
        }

        return sql.toString();
    }


    /**
     * 计算带有相除类型的统计
     */
    private Map<String, Object> getSummaryStat(List datas, KPIMetaData KPIMetaData, String name,
                                               String role, String roleCode,
                                               String areaRole, String areaRoleValue,
                                               String actualRole, String actualRoleValue,
                                               String brand, String startDate, String endDate
    ) {
        Map<String, Object> reMap = Maps.newHashMap();
        reMap.put("KPIAct", 0);
        reMap.put("KPIRank", 0);
        reMap.put("KPIAvg", 0);

        try {
            Double totalValue = 0d, recValue;
            Integer order = 1;
            Map<String, Double> groupValues = new HashMap<>();

            String recAreaCode = null, recRoleCode = null;
            Set<Object> brands = new HashSet<>();
            int count = 0;
            for (int i = 0; i < datas.size(); i++) {
                Map data = (Map) datas.get(i);
                /*if (data.get("大区") == null || data.get("大区").toString().trim().equals("")) continue;*/
                if (!"total".equalsIgnoreCase(areaRole) && !areaRoleValue.equalsIgnoreCase(data.get(areaRole).toString()))
                    continue; //根据对标值筛选统计数据

                count += 1;
                recValue = 0d;

                //在对标值范围内累加
                if (data.get(KPIMetaData.getNumeratorAlias()) != null) {
                    recValue = Double.parseDouble(data.get(KPIMetaData.getNumeratorAlias()).toString());
                }
                totalValue += recValue;

                //在对标值范围内分组累加
                recRoleCode = data.get(actualRole).toString();
                if (groupValues.containsKey(recRoleCode)) {
                    groupValues.put(recRoleCode, groupValues.get(recRoleCode) + recValue);
                } else {
                    groupValues.put(recRoleCode, recValue);
                }
            }

            if (count == 0) {
                reMap.put("KPIAct", 0);
                reMap.put("KPIRank", 0);
                reMap.put("KPIAvg", 0);
            } else {
                recValue = groupValues.get(actualRoleValue);  //获取用户选择实际值对应的统计数据
                if(recValue == null){
                    recValue = 0d;
                }
                for (Double groupValue : groupValues.values()) { //排序
                    if (groupValue.doubleValue() > recValue.doubleValue()) {
                        order += 1;
                    }
                }

                if (TYPE.INT.equals(KPIMetaData.getResultType())) {
                    reMap.put("KPIAct", df_int.format(recValue));
                } else {
                    reMap.put("KPIAct", df_1.format(recValue));
                }
                reMap.put("KPIRank", order + " / " + groupValues.size());

                if (TYPE.INT.equals(KPIMetaData.getResultType())) {
                    reMap.put("KPIAvg", df_int.format(totalValue / groupValues.size()));
                } else {
                    reMap.put("KPIAvg", df_1.format(totalValue / groupValues.size()));
                }
            }
            reMap.put("KPIName", name);
        } catch (Exception exc) {
            logger.error(JsonObjectUtils.writeAsJson(KPIMetaData), exc);
        }

        return reMap;
    }

    /**
     * 计算单个类型的指标
     */
    private Map<String, Object> getUnitStat(List datas, KPIMetaData KPIMetaData, String name,
                                            String role, String roleCode,
                                            String areaRole, String areaRoleValue,
                                            String actualRole, String actualRoleValue,
                                            String brand, String startDate, String endDate) {
        //numerator:分子  denominato:分母
        Double numeratorTotalValue = 0d, denominatorTotalValue = 0d, numeratorValue = 0d, denominatorValue = 0d;
        Map<String, Double> groupNumeratorValues = new HashMap<>(), groupDenominatorValues = new HashMap<>();
        int order = 1;

        Map<String, Object> reMap = Maps.newHashMap();
        reMap.put("numeratorValue", "0");
        reMap.put("denominatorValue", "0");
        reMap.put("KPIAct", "0");
        reMap.put("KPIAvg", 0);
        reMap.put("KPIRank", "0");
        reMap.put("KPIName", name);
        reMap.put("sumA", numeratorTotalValue);
        reMap.put("sumB", denominatorTotalValue);

        try {
            String recAreaCode = null, recRoleCode = null;
            Set<Object> brands = new HashSet<>();
            int count = 0;
            for (int i = 0; i < datas.size(); i++) {
                Map data = (Map) datas.get(i);
                /*if (data.get("大区") == null || data.get("大区").toString().trim().equals("")) continue;*/
                if (!"total".equalsIgnoreCase(areaRole) && !areaRoleValue.equalsIgnoreCase(data.get(areaRole).toString()))
                    continue;

                recRoleCode = data.get(actualRole).toString();
                numeratorValue = 0d;
                denominatorValue = 0d;

                brands.add(recRoleCode + ":" + data.get("品牌"));

                //在对标值范围内累加
                if (data.get(KPIMetaData.getNumeratorAlias()) != null) {
                    numeratorValue = Double.parseDouble(data.get(KPIMetaData.getNumeratorAlias()).toString());
                }
                numeratorTotalValue += numeratorValue;

                if (data.get(KPIMetaData.getDenominatorAlias()) != null) {
                    denominatorValue = Double.parseDouble(data.get(KPIMetaData.getDenominatorAlias()).toString());
                }
                denominatorTotalValue += denominatorValue;

                //在对标值范围内分组累加
                if (groupNumeratorValues.containsKey(recRoleCode)) {
                    groupNumeratorValues.put(recRoleCode, groupNumeratorValues.get(recRoleCode) + numeratorValue);
                } else {
                    groupNumeratorValues.put(recRoleCode, numeratorValue);
                }

                if (groupDenominatorValues.containsKey(recRoleCode)) {
                    groupDenominatorValues.put(recRoleCode, groupDenominatorValues.get(recRoleCode) + denominatorValue);
                } else {
                    groupDenominatorValues.put(recRoleCode, denominatorValue);
                }
            }

            if (denominatorTotalValue == 0 || datas.size() == 0) {
                reMap.put("numeratorValue", "0");
                reMap.put("denominatorValue", "0");
                reMap.put("KPIAct", "0");
                reMap.put("KPIAvg", 0);
                reMap.put("KPIRank", "0");

            } else {
                //获取当前实际值对应的统计数据
                numeratorValue = groupNumeratorValues.get(actualRoleValue);
                if(numeratorValue == null){
                    numeratorValue = 0d;
                }

                denominatorValue = groupDenominatorValues.get(actualRoleValue);
                if(denominatorValue == null){
                    denominatorValue = 0d;
                }

                if(TYPE.INT.equals(KPIMetaData.getNumeratorType())){
                    reMap.put("numeratorValue", df_int.format(NumberUtils.roundInt(numeratorValue)));
                }else{
                    reMap.put("numeratorValue", df_1.format(NumberUtils.round(numeratorValue, 1)));
                }

                if(TYPE.INT.equals(KPIMetaData.getDenominatorType())){
                    reMap.put("denominatorValue", df_int.format(NumberUtils.roundInt(denominatorValue)));
                }else{
                    reMap.put("denominatorValue", df_1.format(NumberUtils.round(denominatorValue, 1)));
                }
                if (Math.abs(denominatorValue) > 0) {
                    if(TYPE.INT.equals(KPIMetaData.getResultType())){
                        reMap.put("KPIAct", df_int.format(NumberUtils.roundInt(numeratorValue / denominatorValue)));
                    }else{
                        reMap.put("KPIAct", df_1.format(NumberUtils.round(numeratorValue / denominatorValue, 1)));
                    }
                } else {
                    reMap.put("KPIAct", 0);
                }

                if (Math.abs(denominatorTotalValue) > 0) {
                    if(TYPE.INT.equals(KPIMetaData.getResultType())){
                        reMap.put("KPIAvg", df_int.format(NumberUtils.roundInt(numeratorTotalValue / denominatorTotalValue)));
                    }else{
                        reMap.put("KPIAvg", df_1.format(NumberUtils.round(numeratorTotalValue / denominatorTotalValue, 1)));
                    }
                } else {
                    reMap.put("KPIAvg", 0);
                }

                Set<String> keys = groupDenominatorValues.keySet();
                for (String key : keys) {
                    if (groupNumeratorValues.get(key) / groupDenominatorValues.get(key) > numeratorValue / denominatorValue) {
                        order += 1;
                    }
                }

                reMap.put("KPIRank", order + " / " + groupDenominatorValues.size());
            }

            reMap.put("KPIName", name);
            if(TYPE.INT.equals(KPIMetaData.getNumeratorType())){
                reMap.put("sumA", df_int.format(NumberUtils.roundInt(numeratorTotalValue)));
            }else{
                reMap.put("sumA", df_1.format(NumberUtils.round(numeratorTotalValue, 1)));
            }

            if(TYPE.INT.equals(KPIMetaData.getDenominatorType())){
                reMap.put("sumB", df_int.format(NumberUtils.roundInt(denominatorTotalValue)));
            }else{
                reMap.put("sumB", df_1.format(NumberUtils.round(denominatorTotalValue, 1)));
            }
        } catch (Exception exc) {
            logger.error(JsonObjectUtils.writeAsJson(KPIMetaData), exc);
        }

        return reMap;
    }

    /**
     * 计算带有率性质的指标计算
     */
    private Map<String, Object> getRatioStat(List datas, KPIMetaData metaData, String name,
                                             String role, String roleCode,
                                             String areaRole, String areaRoleValue,
                                             String actualRole, String actualRoleValue,
                                             String brand, String startDate, String endDate){
        Map<String, Object> reMap = Maps.newHashMap();

        //numerator:分子  denominato:分母
        Double numeratorTotalValue = 0d, denominatorTotalValue = 0d, numeratorValue = 0d, denominatorValue = 0d;
        Map<String, Double> groupNumeratorValues = new HashMap<>(), groupDenominatorValues = new HashMap<>();
        int order = 1;

        if(name.equalsIgnoreCase("订单池比例") || name.equals("当月预计可开票订单池比例")){ //这两个指标只统计当前月的，其他情况下都显示为N/A
            try {
                Date currentDate = new Date();
                String strCurrentDate = DateFormatUtils.getFormatDateToString(currentDate, DateFormatUtils.TIME_FORMAT_STR_YYYY_MM_DD),
                       strCurrentMonth = DateFormatUtils.getFormatDateToString(currentDate, DateFormatUtils.TIME_FORMAT_STR_YYYY_MM);
                if (!(startDate.startsWith(strCurrentMonth) && endDate.startsWith(strCurrentMonth) && endDate.equals(strCurrentDate))) {
                    reMap.put("numeratorValue", "0");
                    reMap.put("denominatorValue", "0");
                    reMap.put("KPIAct", "N/A");
                    reMap.put("KPIAvg", "N/A");
                    reMap.put("KPIRank", "N/A");
                    reMap.put("KPIName", name);
                    reMap.put("sumA", numeratorTotalValue);
                    reMap.put("sumB", denominatorTotalValue);
                    return reMap;
                }
            }catch(Exception exc){
                logger.error("计算异常。", exc);
            }
        }

        reMap.put("numeratorValue", "0");
        reMap.put("denominatorValue", "0");
        reMap.put("KPIAct", "0");
        reMap.put("KPIAvg", 0);
        reMap.put("KPIRank", "0");
        reMap.put("KPIName", name);
        reMap.put("sumA", numeratorTotalValue);
        reMap.put("sumB", denominatorTotalValue);

        try {
            String recRoleCode = null;
            Set<Object> brands = new HashSet<>();
            for (int i = 0; i < datas.size(); i++) {
                numeratorValue = 0d;
                denominatorValue = 0d;

                Map data = (Map) datas.get(i);
                if (!"total".equalsIgnoreCase(areaRole) && !areaRoleValue.equalsIgnoreCase(data.get(areaRole).toString()))
                    continue;

                //在对标值范围内累加
                if (data.get(metaData.getNumeratorAlias()) != null) {
                    numeratorValue = Double.parseDouble(data.get(metaData.getNumeratorAlias()).toString());
                }
                numeratorTotalValue += numeratorValue;

                if (data.get(metaData.getDenominatorAlias()) != null) {
                    denominatorValue = Double.parseDouble(data.get(metaData.getDenominatorAlias()).toString());
                }
                denominatorTotalValue += denominatorValue;

                //在对标值范围内分组累加
                recRoleCode = data.get(actualRole).toString();
                if (groupNumeratorValues.containsKey(recRoleCode)) {
                    groupNumeratorValues.put(recRoleCode, groupNumeratorValues.get(recRoleCode) + numeratorValue);
                } else {
                    groupNumeratorValues.put(recRoleCode, numeratorValue);
                }

                if (groupDenominatorValues.containsKey(recRoleCode)) {
                    groupDenominatorValues.put(recRoleCode, groupDenominatorValues.get(recRoleCode) + denominatorValue);
                } else {
                    groupDenominatorValues.put(recRoleCode, denominatorValue);
                }
            }

            if (denominatorTotalValue == 0 || datas.size() == 0) {
                reMap.put("numeratorValue", "0");
                reMap.put("denominatorValue", "0");
                reMap.put("KPIAct", "0");
                reMap.put("KPIAvg", 0);
                reMap.put("KPIRank", "0");
            } else {
                //获取当前实际值对应的统计数据
                numeratorValue = groupNumeratorValues.get(actualRoleValue);
                if(numeratorValue == null){
                    numeratorValue = 0d;
                }

                denominatorValue = groupDenominatorValues.get(actualRoleValue);
                if(denominatorValue == null){
                    denominatorValue = 0d;
                }

                if(TYPE.INT.equals(metaData.getNumeratorType())){
                    reMap.put("numeratorValue", df_int.format(NumberUtils.roundInt(numeratorValue)));
                }else{
                    reMap.put("numeratorValue", df_1.format(NumberUtils.round(numeratorValue, 1)));
                }

                if(TYPE.INT.equals(metaData.getDenominatorType())){
                    reMap.put("denominatorValue", df_int.format(NumberUtils.roundInt(denominatorValue)));
                }else{
                    reMap.put("denominatorValue", df_1.format(NumberUtils.round(denominatorValue, 1)));
                }

                if (Math.abs(denominatorValue) > 0 && Math.abs(numeratorValue) > 0) {
                    if(TYPE.INT.equals(metaData.getResultType())){
                        reMap.put("KPIAct", df_int.format(NumberUtils.roundInt(100 * numeratorValue / denominatorValue)));
                    }else{
                        if("销售目标与新增展厅客流比".equalsIgnoreCase(name)){
                            reMap.put("KPIAct", "1:" + df_1.format(NumberUtils.round(denominatorValue / numeratorValue, 1)));
                        }else {
                            reMap.put("KPIAct", df_1.format(NumberUtils.round(100 * numeratorValue / denominatorValue, 1)));
                        }
                    }
                } else {
                    if("销售目标与新增展厅客流比".equalsIgnoreCase(name)){
                        reMap.put("KPIAct", "0:0");
                    }else {
                        reMap.put("KPIAct", 0);
                    }
                }

                if (Math.abs(denominatorTotalValue) > 0 && Math.abs(numeratorTotalValue) > 0) {
                    if(TYPE.INT.equals(metaData.getResultType())){
                        reMap.put("KPIAvg", df_int.format(NumberUtils.roundInt(100 * numeratorTotalValue / denominatorTotalValue)));
                    }else{
                        if("销售目标与新增展厅客流比".equalsIgnoreCase(name)){
                            reMap.put("KPIAvg", "1:" + df_1.format(NumberUtils.round(denominatorTotalValue/numeratorTotalValue, 1)));
                        }else {
                            reMap.put("KPIAvg", df_1.format(NumberUtils.round(100 * numeratorTotalValue / denominatorTotalValue, 1)));
                        }
                    }
                } else {
                    if("销售目标与新增展厅客流比".equalsIgnoreCase(name)){
                        reMap.put("KPIAvg", "0:0");
                    }else{
                        reMap.put("KPIAvg", 0);
                    }
                }

                Set<String> keys = groupDenominatorValues.keySet();
                for (String key : keys) {
                    if (groupNumeratorValues.get(key) / groupDenominatorValues.get(key) > numeratorValue / denominatorValue) {
                        order += 1;
                    }
                }

                reMap.put("KPIRank", order + " / " + groupDenominatorValues.size());
            }

            reMap.put("KPIName", name);
            if(TYPE.INT.equals(metaData.getNumeratorType())){
                reMap.put("sumA", df_int.format(NumberUtils.roundInt(numeratorTotalValue)));
            }else{
                reMap.put("sumA", df_1.format(NumberUtils.round(numeratorTotalValue, 1)));
            }

            if(TYPE.INT.equals(metaData.getDenominatorType())){
                reMap.put("sumB", df_int.format(NumberUtils.roundInt(denominatorTotalValue)));
            }else{
                reMap.put("sumB", df_1.format(NumberUtils.round(denominatorTotalValue, 1)));
            }

            /*reMap.put("sumA", numeratorTotalValue);
            reMap.put("sumB", denominatorTotalValue);*/
        } catch (Exception exc) {
            logger.error(JsonObjectUtils.writeAsJson(metaData), exc);
        }

        return reMap;
    }

    /**
     * 对标值过滤
     */
    private List<Map<String, Object>> filter(List<Map<String, Object>> origin, String areaRole, String areaCode) {
        if (origin == null || origin.isEmpty()) return origin;
        List filtered = new ArrayList();
        for (Map<String, Object> data : origin) {
            if (areaCode.equalsIgnoreCase((String) data.get(areaRole))) {
                filtered.add(data);
            }
        }
        return filtered;
    }

    public List getAreaForDealer(String permit) {
        String sql = "SELECT 许可_bmbs, 大区, 大区_EN, 小区, 小区_EN, CTA, CTA_EN ,'TOTAL' TOTAL " +
                "FROM LSH经销商表 " +
                "where (经销商状态 <> '已删除' or 经销商状态 is null) " +
                "and 许可_bmbs = :permit";
        Map<String, String> params = new HashMap<>();
        params.put("permit", permit);
        return getList(sql, params);
    }

    public List getAreaForDealerTwo(String permit) {
        String sql = "SELECT  大区, 大区_EN,'TOTAL' TOTAL   " +
                "FROM LSH经销商表 " +
                "WHERE CTA = :permit " +
                " limit 1 ";
        Map<String, String> params = new HashMap<>();
        params.put("permit", permit);
        return getList(sql, params);
    }

    public List getAreaForDealerThree(String permit) {
        String sql = "SELECT  'TOTAL' TOTAL  " +
                "FROM LSH经销商表 LIMIT 1  ";
        return getList(sql);
    }

    @Override
    public List getPermitWithSameLshCoder(String permit) {
        String sql = "SELECT  " +
                "    `许可_BMBS`, " +
                "    许可_lsh, " +
                "    IF(`经销商` <> '', `经销商`, `经销商名`) `经销商` " +
                "FROM `LSH经销商表` " +
                "WHERE `许可_LSH` = (SELECT `许可_LSH` FROM `LSH经销商表` WHERE `许可_BMBS` = :permit) ";
        Map<String, String> params = new HashMap<>();
        params.put("permit", permit);
        return this.getList(sql, params);
    }

    @Override
    public List getApprovalUserInfo(String lshPermit, String type) {
        // 先把审批设置中相关的记录取出来
        String sql = "select * " +
                "from pushApprovalItemSetting " +
                "where permit = :lshPermit " +
                "and type = :type ";
        Map<String, String> params = new HashMap<>();
        params.put("lshPermit", lshPermit);
        params.put("type", type);
        List list = getList(sql, params);

        if (list.size() == 0) {
            return null;
        }

        Integer id = (Integer) ((Map) list.get(0)).get("id");
        Date createDate = (Date) ((Map) list.get(0)).get("createDate");
        Date modifyDate = (Date) ((Map) list.get(0)).get("modifydate");
        String remark = (String) ((Map) list.get(0)).get("remark");

        // 再通过与用户表关联取其他信息
        String userNameSql = "select " +
                "    `许可`, " +
                "    `所属部门`, " +
                "    `角色`, " +
                "    `用户名`, " +
                "    `人员姓名`, " +
                "    `人员电话` " +
                "from 用户 " +
                "where 1 = 1 ";
        String reSql = "";
        String[] s = new String[10];
        Integer[] t = new Integer[10];
        List<Map> tmpList = new ArrayList<>();
        int i = 1;
        while (true) {
            String user = (String) ((Map) list.get(0)).get("step" + i);
            // 将所有的审批人暂存
            s[i] = user;
            if (user == null) {
                break;
            } else {
                if (setService.isRoleName(user)) { // 邮箱审批时
                    if (reSql.length() != 0) {
                        reSql += "union all ";
                        reSql = reSql + userNameSql + "and 用户名 = " + "\"" + user + "\" ";
                    } else {
                        reSql = reSql + userNameSql + "and 用户名 = " + "\"" + user + "\" ";
                    }
                } else {
                    Map<String, String> map = new HashMap<>();
                    map.put("许可", lshPermit);
                    map.put("所属部门", user.split("_")[0]);
                    map.put("角色", user.split("_")[1]);
                    map.put("用户名", "");
                    map.put("人员姓名", "");
                    map.put("人员电话", "");
                    tmpList.add(map);
                }
                i++;
            }
        }
        List reList;
        if (reSql.length() == 0) {
            reList = new ArrayList();
        } else {
            reList = getList(reSql);
        }
        reList.addAll(tmpList);
        for (Object o : reList) {
            Map m = (Map) o;
            // 遍历s[i]的时候，每次要重头开始
            i = 0;
            while (!(m.get("用户名").equals(s[i]) || (m.get("所属部门") + "_" + m.get("角色")).equals(s[i]))) {
                i++;
            }
            m.put("step", "step" + i);
            m.put("id", id);
            m.put("createDate", createDate);
            m.put("modifyDate", modifyDate);
            m.put("remark", remark);
            s[i] = null;    // 将已经取出的审批人置空，防止相同的审批人影响数据
        }
        return reList;
    }

    @Override
    public ResultData getApprovalUserInfoBrand(String permit, String type) {
        ResultData resultData = new ResultData();
        resultData.setResult("true");
        resultData.setMessage("正常");
        String sql = "select a.id,  " +
                "   a.permit as 经销商代码,   " +
//                "   a.品牌,   " +
                "   a.审批级别,   " +
                "   b.所属部门,   " +
                "   b.角色,   " +
                "   b.用户名,   " +
                "   b.人员姓名,   " +
                "   b.人员电话,   " +
                "   d.经销商名,   " +
                "   d.大区 as 所属区域   " +
                "from (  " +
                "	select id, permit, step1 as step, '一级批复' as 审批级别 from pushApprovalItemSetting where type = :type and step1 is not null  " +
                "	union all  " +
                "	select id, permit, step2 as step, '二级批复' as 审批级别 from pushApprovalItemSetting where type = :type and step2 is not null  " +
                "	union all  " +
                "	select id, permit, step3 as step, '三级批复' as 审批级别 from pushApprovalItemSetting where type = :type and step3 is not null  " +
                "	union all  " +
                "	select id, permit, step4 as step, '四级批复' as 审批级别 from pushApprovalItemSetting where type = :type and step4 is not null  " +
                "	union all  " +
                "	select id, permit, step5 as step, '五级批复' as 审批级别 from pushApprovalItemSetting where type = :type and step5 is not null  " +
                "	union all  " +
                "	select id, permit, step6 as step, '六级批复' as 审批级别 from pushApprovalItemSetting where type = :type and step6 is not null  " +
                "	union all  " +
                "	select id, permit, step7 as step, '七级批复' as 审批级别 from pushApprovalItemSetting where type = :type and step7 is not null  " +
                ") a  " +
                "left join 用户 b on a.step = b.用户名  " +
                "left join lsh经销商表 d on a.permit = d.许可_bmbs    " +
                "where a.permit = :permit    ";
        Map<String, String> params = Maps.newHashMap();
        params.put("permit", permit);
        params.put("type", type);
        List list = getList(sql, params);
        if (list.size() == 0) {
            resultData.setMessage("返回数据为空");
        }
        PageData pageData = new PageData(list, 0);
        resultData.setPageData(pageData);
        return resultData;
    }

    @Override
    public List getContractFlowSetting(Integer type) {
        String sql = "select id, permit, step1 as userOrPosition, 'step1' as step, '销售顾问' as 审批级别 from pushApprovalItemSetting where type = 3 and step1 is not null  \n" +
                "union all  \n" +
                "select id, permit, step2 as userOrPosition, 'step2' as step, '一级审批' as 审批级别 from pushApprovalItemSetting where type = 3 and step2 is not null  \n" +
                "union all  \n" +
                "select id, permit, step3 as userOrPosition, 'step3' as step, '二级审批' as 审批级别 from pushApprovalItemSetting where type = 3 and step3 is not null  \n" +
                "union all  \n" +
                "select id, permit, step4 as userOrPosition, 'step4' as step, '三级审批' as 审批级别 from pushApprovalItemSetting where type = 3 and step4 is not null  \n" +
                "union all  \n" +
                "select id, permit, step5 as userOrPosition, 'step5' as step, '四级审批' as 审批级别 from pushApprovalItemSetting where type = 3 and step5 is not null  \n" +
                "union all  \n" +
                "select id, permit, step6 as userOrPosition, 'step6' as step, '五级审批' as 审批级别 from pushApprovalItemSetting where type = 3 and step6 is not null  \n" +
                "union all  \n" +
                "select id, permit, step7 as userOrPosition, 'step7' as step, '六级审批' as 审批级别 from pushApprovalItemSetting where type = 3 and step7 is not null  \n" +
                "union all  \n" +
                "select id, permit, step8 as userOrPosition, 'step8' as step, '七级审批' as 审批级别 from pushApprovalItemSetting where type = 3 and step8 is not null  \n" +
                "union all  \n" +
                "select id, permit, step9 as userOrPosition, 'step9' as step, '八级审批' as 审批级别 from pushApprovalItemSetting where type = 3 and step9 is not null  \n";
        return getList(sql);
    }

    @Override
    public List getPositionByDepartment(String permit, String department) {
        String sql = "SELECT DISTINCT(角色) 角色 " +
                "FROM `用户` " +
                "WHERE 许可 = :permit " +
                "AND 所属部门 = :department " +
                "AND 角色 <> '' " +
                "and 删除状态 <> '已删除' ";
        Map<String, String> params = new HashMap<>();
        params.put("permit", permit);
        params.put("department", department);
        return getList(sql, params);
    }

    @Override
    public List getUserNameByPosition(String permit, String department, String role) {
        String sql = "SELECT " +
                "   DISTINCT(a.`用户名`) `用户名`, " +
                "   b.`人员姓名`, " +
                "   b.`人员电话` " +
                "FROM `用户` AS a " +
                "LEFT JOIN `用户` AS b ON b.`用户名` = a.`用户名` AND b.`许可` = a.`许可` " +
                "WHERE a.许可 = :permit " +
                "AND a.所属部门 = :department " +
                "AND a.角色 = :role " +
                "AND a.用户名 <> '' " +
                "and a.删除状态 <> '已删除' ";
        Map<String, String> params = new HashMap<>();
        params.put("permit", permit);
        params.put("department", department);
        params.put("role", role);
        return getList(sql, params);
    }

    @Override
    public List getDepartmentByShowRoom(String permit) {
        String sql = "SELECT DISTINCT(`所属部门`) `所属部门` " +
                "FROM `用户` " +
                "WHERE 许可 = :permit " +
                "AND 所属部门 <> '' " +
                "and 删除状态 <> '已删除' ";
        Map<String, String> params = new HashMap<>();
        params.put("permit", permit);
        return getList(sql, params);
    }

    @Override
    public List getCarSaleInfoes(String year, String month) {
        /*String sql = String.format("SELECT 集团, 大区, 大区_EN, CTA,CTA_EN, 许可_BMBS,许可_LSH, 经销商名,经销商名_EN, IFNULL(SUM(新车销量),0) 新车销量 FROM (%s) a GROUP BY 许可_BMBS", KPIUtils.newCarSaleAmount);

        return getList(sql.toString().replace("'MB'", "''")
                .replace("2000-01-01", DateFormatUtils.getFirstDayOfMonth(year, month))
                .replace("2020-01-01", DateFormatUtils.getEndDayOfMonth(year, month)));*/

        return null;
    }

    private boolean isChineseLanguage(){
        return "CN".equalsIgnoreCase(ThreadContext.get("LIVE_LANGUAGE"));
    }
}
